package com.jdbc;

import java.sql.*;
import java.util.Scanner;

/**
 * @author 1
 * @Description
 * @Date 2025-2-24 17:21
 */
public class HomeworkDemo {
    public static final String URL = "jdbc:mysql://localhost:3306/mydb?serverTimezone=GMT&useUnicode=true&characterEncoding=UTF-8";
    public static final String USER = "root";
    public static final String PASSWORD = "123456";

    /**
     *登陆方法
     */
    public static boolean login(String username,String password){
        //1.获取连接
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            //2.定义sql
            String sql = "select * from t_user where username = ? and password =?";
            //3.获得命令对象
            PreparedStatement statement = connection.prepareStatement(sql);
            //4.设置参数
            statement.setString(1,username);
            statement.setString(2,password);
            //5.执行查询命令
            ResultSet resultSet = statement.executeQuery();
            //6.判断是否有匹配的账号密码
            if(resultSet.next()){
                return true;
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 打印选择菜单方法
     */
    public static void showMenu(Scanner scanner){
        while (true) {
            System.out.println("\n请选择操作：");
            System.out.println("1) 显示所有学生");
            System.out.println("2) 添加学生");
            System.out.println("3) 删除学生");
            System.out.println("4) 更新学生");
            System.out.println("0) 退出");
            System.out.print("请输入选项: ");
            int choice = scanner.nextInt();
            scanner.nextLine();
            switch (choice) {
                case 1:
                    showStudent();
                    break;
                case 2:
                    addStudent(scanner);
                    break;
                case 3:
                    deleteStudent(scanner);
                    break;
                case 4:
                    updateStudent(scanner);
                    break;
                case 0:
                    System.out.println("退出系统");
                    return;
                default:
                    System.out.println("无效选项，请重新选择！");
            }
        }
    }

    /**
     *显示所有学生方法
     */
    public static void showStudent(){
        //通过驱动管理器获得连接
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            //通过连接对象获得命令对象
            Statement statement = connection.createStatement();
            //实现数据查询
            String sql = "select * from student";
            ResultSet resultSet = statement.executeQuery(sql);
            //遍历结果集
            while(resultSet.next()){
                //读取每一列数据
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");
                String cellphone = resultSet.getString("cellphone");
                String address = resultSet.getString("address");
                System.out.println("id:"+id+",name:"+name+",age:"+age+",gender:"+gender+",cellphone:"+cellphone+",address:"+address);
            }
            resultSet.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 添加学生方法
     */
    public static void addStudent(Scanner scanner) {
        System.out.println("请输入学生姓名: ");
        String name = scanner.nextLine();
        System.out.println("请输入学生年龄: ");
        int age = scanner.nextInt();
        scanner.nextLine();
        System.out.println("请输入学生性别: ");
        String gender = scanner.nextLine();
        System.out.println("请输入学生电话: ");
        String cellphone = scanner.nextLine();
        System.out.println("请输入学生地址: ");
        String address = scanner.nextLine();
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            //实现数据插入
            String sql = "insert into student(name,age,gender,cellphone,address) values(?,?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //设置参数
            preparedStatement.setString(1,name);
            preparedStatement.setInt(2,age);
            preparedStatement.setString(3,gender);
            preparedStatement.setString(4,cellphone);
            preparedStatement.setString(5,address);
            int rows = preparedStatement.executeUpdate();
            if (rows > 0) {
                System.out.println("数据插入成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 删除学生方法
     */
    public static void deleteStudent(Scanner scanner){
        System.out.println("请输入要删除的学生id: ");
        int id = scanner.nextInt();
        scanner.nextLine();
        //通过驱动管理器获得连接
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            String sql = "delete from student where id= ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            int rows = preparedStatement.executeUpdate();
            if (rows > 0) {
                System.out.println("学生删除成功！");
            }else{
                System.out.println("未找到该学生！");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 更新学生方法
     */
    public static void updateStudent(Scanner scanner){
        System.out.println("请输入要更新的学生姓名: ");
        String name = scanner.nextLine();
        System.out.println("请输入要更新的学生年龄: ");
        int age = scanner.nextInt();
        scanner.nextLine();
        System.out.println("请输入要更新的学生性别: ");
        String gender = scanner.nextLine();
        System.out.println("请输入要更新的学生电话: ");
        String cellphone = scanner.nextLine();
        System.out.println("请输入要更新的学生地址: ");
        String address = scanner.nextLine();
        System.out.println("请输入要更新的学生id: ");
        int id = scanner.nextInt();
        scanner.nextLine();
        //通过驱动管理器获得连接
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            //实现数据插入
            String sql = "update student set name= ?,age= ?,gender= ?,cellphone= ?,address= ? where id= ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //设置参数
            preparedStatement.setString(1,name);
            preparedStatement.setInt(2,age);
            preparedStatement.setString(3,gender);
            preparedStatement.setString(4,cellphone);
            preparedStatement.setString(5,address);
            preparedStatement.setInt(6,id);
            int rows = preparedStatement.executeUpdate();
            if (rows > 0) {
                System.out.println("学生信息更新成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = scanner.nextLine();
        System.out.println("请输入密码:");
        String password = scanner.nextLine();
        if(login(username,password)){
            System.out.println("登陆成功");
            showMenu(scanner);
        }else{
            System.out.println("登陆失败");
        }
    }
}
